﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace SMS_Server
{
    /// <summary>
    /// Must inherit abstract class DataInit and interface iDataAccess
    /// </summary>
    public class SQLDataAccess : iDataAccess
    {
        //Database Variables
        private SqlConnection Connection;
        private SqlCommand Command;
        private SqlDataReader reader;

        /// <summary>
        /// Get or Set the CommandString to execute
        /// </summary>
        public override string CommandString
        {
            get
            {
                return Command.CommandText;
            }
            set
            {
                Command.CommandText = value;
            }
        }

        /// <summary>
        /// Returns the connection state of the OleDbDataAccess2003 object
        /// </summary>
        public override System.Data.ConnectionState State
        {
            get
            {
                return Connection.State;
            }
        }

        /// <summary>
        /// Add OleDbParameter
        /// </summary>
        /// <param name="ParamName"></param>
        /// <param name="value"></param>
        public override void AddParameter(string ParamName, object value)
        {
            Command.Parameters.Add(new SqlParameter(ParamName, value));
        }

        /// <summary>
        /// Change the value of the parameter by it's name
        /// </summary>
        /// <param name="ParamName"></param>
        /// <param name="value"></param>
        public override void SetParameter(string ParamName, object value)
        {
            Command.Parameters[ParamName].Value = value;
        }

        /// <summary>
        /// Remove a parameter by it's name
        /// </summary>
        /// <param name="ParamName"></param>
        public override void RemoveParameter(string ParamName)
        {
            Command.Parameters.Remove(Command.Parameters[ParamName]);
        }

        /// <summary>
        /// Get Parameter value by it's name
        /// </summary>
        /// <param name="ParamName"></param>
        /// <returns></returns>
        public override object GetParameter(string ParamName)
        {
            return Command.Parameters[ParamName].Value;
        }

        /// <summary>
        /// Clear all command Parameters
        /// </summary>
        public override void ClearParameters()
        {
            Command.Parameters.Clear();
        }

        /// <summary>
        /// Get the OleDbDataReader
        /// </summary>
        public override System.Data.IDataReader Reader
        {
            get
            {
                return reader;
            }
        }

        /// <summary>
        /// Execute the Command and make the reader ready
        /// </summary>
        public override void ExecuteReader()
        {
            if (reader != null)
            {
                if (!reader.IsClosed)
                {
                    reader.Close();
                }
            }
            if (Connection.State != System.Data.ConnectionState.Open) Connection.Open();
            reader = Command.ExecuteReader();
        }
        /// <summary>
        /// Dataset That will be returned to provide data access to some cases
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSet()
        {
            if (Connection.State != System.Data.ConnectionState.Open) Connection.Open();
            SqlDataAdapter DAdaprter = new SqlDataAdapter(Command);
            DataSet Ds = new DataSet();
            DAdaprter.Fill(Ds);
            return Ds;
        }

        /// <summary>
        /// Execute the command in NonQuery mode
        /// </summary>
        public override void ExecuteNonQuery()
        {
            if (Connection.State != System.Data.ConnectionState.Open) Connection.Open();
            Command.ExecuteNonQuery();
        }

        /// <summary>
        /// Close the connection
        /// </summary>
        public override void Close()
        {
            if (Connection.State != System.Data.ConnectionState.Closed) Connection.Close();
        }

        /// <summary>
        /// A call has been made for OleDbDataAccess
        /// </summary>
        public SQLDataAccess(string ConnectionString)
            : base(ConnectionString)
        {
            Connection = new SqlConnection(ConnectionString);
            Command = new SqlCommand("", Connection);
        }

        /// <summary>
        /// Dispose the object
        /// </summary>
        ~SQLDataAccess()
        {
            //if (Connection.State != System.Data.ConnectionState.Closed) Connection.Close();
        }

    }
}